﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyHocPhi.Entity;

/// <summary>
/// Summary description for StudentDAO
/// </summary>
namespace QuanLyHocPhi.DAL
{
    public class SINH_VIEN_DAO
    {
        private const string SQL_INSERT = "insert into SINH_VIEN(MA_SO_SV,TEN_SV,NGAY_SINH,DIA_CHI,SO_DIEN_THOAI,EMAIL,GIOI_TINH) values(@MA_SO_SV, @TEN_SV, @NGAY_SINH, @DIA_CHI, @SO_DIEN_THOAI, @EMAIL, @GIOI_TINH)";
        private const string SQL_UPDATE = "update SINH_VIEN set MA_SO_SV = @MA_SO_SV, TEN_SV = @TEN_SV, NGAY_SINH = @NGAY_SINH, DIA_CHI = @DIA_CHI, SO_DIEN_THOAI = @SO_DIEN_THOAI, EMAIL = @EMAIL, GIOI_TINH = @GIOI_TINH where MA_SV = @MA_SV";
        private const string SQL_DELETE = "delete from SINH_VIEN where MA_SV = @MA_SV";
        private const string SQL_SELECT_ALL = "SELECT * FROM SINH_VIEN";
        private const string SQL_SELECT_BY_NAME_OR_CODE = "SELECT  * from SINH_VIEN where TEN_SV like @TEN_SV or MA_SO_SV like @MA_SO_SV";
        private const string SQL_SELECT_BY_CLASS = "SELECT  SINH_VIEN.MA_SV, SINH_VIEN.MA_SO_SV, SINH_VIEN.TEN_SV, SINH_VIEN.NGAY_SINH, SINH_VIEN.DIA_CHI, SINH_VIEN.SO_DIEN_THOAI, "
            + "  SINH_VIEN.EMAIL, SINH_VIEN.GIOI_TINH"
            + " FROM SINH_VIEN INNER JOIN  SINHVIEN_LOPHOC ON SINH_VIEN.MA_SV = SINHVIEN_LOPHOC.MA_SV"
            + " where MA_LOP_HOC = @MA_LOP_HOC";
        private const string SQL_SELECT_SINH_VIEN = "select SINH_VIEN from SINH_VIEN  where TEN_SV = @TEN_SV";

        // Parameters
        private const string PARM_STUDENT_ID = "@MA_SV";
        private const string PARM_STUDENT_CODE = "@MA_SO_SV";
        private const string PARM_STUDENT_NAME = "@TEN_SV";
        private const string PARM_STUDENT_NGAY_SINH = "@NGAY_SINH";
        private const string PARM_STUDENT_DIA_CHI = "@DIA_CHI";
        private const string PARM_STUDENT_SO_DIEN_THOAI = "@SO_DIEN_THOAI";
        private const string PARM_STUDENT_EMAIL = "@EMAIL";
        private const string PARM_STUDENT_GIOI_TINH = "@GIOI_TINH";
        private const string PARM_CLASS_ID = "@GIOI_TINH";

        #region Public Methods
        public IList<SINH_VIEN> GetStudentsByNameOrCode(string key)
        {
            IList<SINH_VIEN> SINHVIEN = new List<SINH_VIEN>();
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_STUDENT_NAME, SqlDbType.NVarChar),
											 new SqlParameter(PARM_STUDENT_CODE, SqlDbType.NVarChar)};
            parms[0].Value = "%" + key + "%";
            parms[1].Value = "%" + key + "%";
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_BY_NAME_OR_CODE, parms))
            {
                while (reader.Read())
                {
                    SINH_VIEN sinhvien = new SINH_VIEN();
                    sinhvien.MA_SV = reader.GetInt32(reader.GetOrdinal("MA_SV"));
                    sinhvien.MA_SO_SV = reader.GetString(reader.GetOrdinal("MA_SO_SV"));
                    sinhvien.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));
                    sinhvien.NGAY_SINH = reader.GetDateTime(reader.GetOrdinal("NGAY_SINH"));
                    sinhvien.DIA_CHI = reader.GetString(reader.GetOrdinal("DIA_CHI"));
                    sinhvien.EMAIL = reader.GetString(reader.GetOrdinal("EMAIL"));
                    sinhvien.SO_DIEN_THOAI = reader.GetString(reader.GetOrdinal("SO_DIEN_THOAI"));
                    sinhvien.GIOI_TINH = reader.GetBoolean(reader.GetOrdinal("GIOI_TINH"));
                    SINHVIEN.Add(sinhvien);
                }
            }
            return SINHVIEN;
        }

        public IList<SINH_VIEN> GetStudentsByClass(LOP_HOC cls)
        {
            IList<SINH_VIEN> SINHVIEN = new List<SINH_VIEN>();
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_CLASS_ID, SqlDbType.Int)};
            parms[0].Value = cls.MA_LOP_HOC;
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_BY_CLASS, parms))
            {
                while (reader.Read())
                {
                    SINH_VIEN SV = new SINH_VIEN();
                    SV.MA_SV = reader.GetInt32(reader.GetOrdinal("MA_SV"));
                    SV.MA_SO_SV = reader.GetString(reader.GetOrdinal("MA_SO_SV"));
                    SV.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));
                    SV.NGAY_SINH = reader.GetDateTime(reader.GetOrdinal("NGAY_SINH"));
                    SV.DIA_CHI = reader.GetString(reader.GetOrdinal("DIA_CHI"));
                    SV.EMAIL = reader.GetString(reader.GetOrdinal("EMAIL"));
                    SV.SO_DIEN_THOAI = reader.GetString(reader.GetOrdinal("SO_DIEN_THOAI"));
                    SV.GIOI_TINH = reader.GetBoolean(reader.GetOrdinal("GIOI_TINH"));
                    SINHVIEN.Add(SV);
                }
            }
            return SINHVIEN;
        }

        public Boolean Insert(SINH_VIEN SV, out int newId)
        {
            {
                SqlParameter[] parms;
                parms = new SqlParameter[] {
											 new SqlParameter(PARM_STUDENT_CODE, SqlDbType.NVarChar),
											 new SqlParameter(PARM_STUDENT_NAME, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_STUDENT_NGAY_SINH, SqlDbType.DateTime),
                                             new SqlParameter(PARM_STUDENT_DIA_CHI, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_STUDENT_SO_DIEN_THOAI, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_STUDENT_EMAIL, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_STUDENT_GIOI_TINH, SqlDbType.Bit)};
                parms[0].Value = SV.MA_SO_SV;
                parms[1].Value = SV.TEN_SV;
                parms[2].Value = SV.NGAY_SINH;
                parms[3].Value = SV.DIA_CHI;
                parms[4].Value = SV.SO_DIEN_THOAI;
                parms[5].Value = SV.EMAIL;
                parms[6].Value = SV.GIOI_TINH;

                try
                {
                    int result = 0;
                    SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                    result = SQLDBHelper.ExecuteNonQuery(conn,
                        CommandType.Text, SQL_INSERT, parms);
                    newId = int.Parse(SQLDBHelper.ExecuteScalar(conn, CommandType.Text, "select @@IDENTITY from SINH_VIEN", null).ToString()); ;
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    return (result > 0 ? true : false);
                }
                catch (Exception e)
                {
                    throw e;
                }
                //return false;
            }
        }
        
        public bool Update(SINH_VIEN student)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_STUDENT_CODE, SqlDbType.NVarChar),
											 new SqlParameter(PARM_STUDENT_NAME, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_STUDENT_NGAY_SINH, SqlDbType.DateTime),
                                             new SqlParameter(PARM_STUDENT_DIA_CHI, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_STUDENT_SO_DIEN_THOAI, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_STUDENT_EMAIL, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_STUDENT_GIOI_TINH, SqlDbType.Bit),
                                             new SqlParameter(PARM_STUDENT_ID, SqlDbType.Int)};
            parms[0].Value = student.MA_SO_SV;
            parms[1].Value = student.TEN_SV;
            parms[2].Value = student.NGAY_SINH;
            parms[3].Value = student.DIA_CHI;
            parms[4].Value = student.SO_DIEN_THOAI;
            parms[5].Value = student.EMAIL;
            parms[6].Value = student.GIOI_TINH;
            parms[7].Value = student.MA_SV;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool Delete(int id)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_STUDENT_ID, SqlDbType.Int) };
            parms[0].Value = id;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }
        #endregion
    }
}